package uk.ac.aber.paws.server.db;

import uk.ac.aber.paws.client.db.DBCall;
import uk.ac.aber.paws.server.db.DBConnectionObject;
import uk.ac.aber.paws.server.db.ParentDBOps;
import uk.ac.aber.paws.server.util.TimeChecks;
import static uk.ac.aber.paws.server.config.NetConstants.*;

import uk.ac.aber.paws.server.config.*;
import uk.ac.aber.paws.server.core.PAWSInstaller;
import uk.ac.aber.paws.server.core.PAWSServer;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.*;
import java.util.logging.Level;

/**
 * @author Rob Johnson, Aberystwyth University
 * @email rob.johnson@aber.ac.uk
 * @date 21st May 2012
 * 
 * DBLogger.java
 * 
 * This class stores all the procedures for invoking queries on the database.
 * 
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
public class DBLogger extends ParentDBOps {

	/**
	 * Return all power management profiles from the database
	 * @return A vector containing the results
	 */
	public Vector<Hashtable<String,Object>> getAllProfiles() {
		
		Vector<Hashtable<String,Object>> returnObj = new Vector<Hashtable<String,Object>>();
		
		Vector<Object> params = new Vector<Object>();
		
		DBCall dbCall = new DBCall(DBCall.QUERY,"Profiles_Get",params,true,true);
		
		PAWSServer.getDBLocalCalls().makeImmediateDBCall(dbCall);
		
		try {
			
			Vector<Hashtable<String,Object>> results = (Vector<Hashtable<String,Object>>)dbCall.getResponse().get("QUERYRESULT");
			
			for (int i=0;i<results.size();i++) {
				
				Hashtable<String,Object> dataHash = results.get(i);
				
				returnObj.add(dataHash);
			}
			
		}
		catch (Exception e) {
			e.printStackTrace();
		}
	
		return returnObj;
	}

	/**
	 * Updates the install status for a remote update job
	 * @param id The id of the update job
	 * @param status The status of the job
	 */
	public void updateInstallStatus(long id, int status) {
		
		Vector<Object> params = new Vector<Object>();
		
		params.add(id);
		params.add(status);
		
		DBCall dbCall = new DBCall(DBCall.UPDATE,"RemoteInstalls_Update",params,true,true);
		
		PAWSInstaller.DBLocalCalls.makeImmediateDBCall(dbCall);	
	}
	
	/**
	 * Returns a list of installs that of a certain status
	 * @param The fully qualified domain in which to query installs for
	 * @param status The job status in which to query
	 * @return A vector of hashes containing the list of update jobs
	 */
	public Vector<Hashtable<String,Object>> getPendingInstalls(String domain, int status) {
		
		Vector<Hashtable<String,Object>> returnObj = new Vector<Hashtable<String,Object>>();
		
		Vector<Object> params = new Vector<Object>();
		
		params.add(domain);
		params.add(status);
		
		DBCall dbCall = new DBCall(DBCall.QUERY,"RemoteInstalls_Get",params,true,true);
		
		PAWSInstaller.DBLocalCalls.makeImmediateDBCall(dbCall);
		
		try {
			
			Vector<Hashtable<String,Object>> results = (Vector<Hashtable<String,Object>>)dbCall.getResponse().get("QUERYRESULT");
			
			for (int i=0;i<results.size();i++) {
				
				Hashtable<String,Object> dataHash = results.get(i);
				
				returnObj.add(dataHash);
			}
			
		}
		catch (Exception e) {
			e.printStackTrace();
		}
	
		return returnObj;
	}

	/**
	 * Purges the power event logs from the relevant table
	 * @param cutOffDate The date in which to purge the events before
	 */
	public void purgePowerEventLogs(Calendar cutOffDate) {
		
		Vector<Object> params = new Vector<Object>();
		params.add(new Timestamp(cutOffDate.getTimeInMillis()));
		
		DBCall dbCall = new DBCall(DBCall.QUERY,"PowerEventLogs_Purge",params,true,true);
		
		PAWSServer.getDBLocalCalls().makeImmediateDBCall(dbCall);
	}
	
	/**
	 * Archives power event logs
	 * @param computerList The computer list (delimited by delim) in which to archive power event stats for
	 * @param delim The delimiter used to separate the individual fqdn names
	 * @param ou The ou in which to index the archived stats bu
	 * @param fromDate The from date in which to archive the stats from
	 * @param toDate The to date in which to archive the stats to
	 */
	public void archiveEventLogs(String computerList, String delim, String ou, Calendar fromDate, Calendar toDate) {
		
		Vector<Object> params = new Vector<Object>();
		params.add(computerList);
		params.add(delim);
		params.add(ou);
		params.add(new Timestamp(fromDate.getTimeInMillis()));
		params.add(new Timestamp(toDate.getTimeInMillis()));
		
		DBCall dbCall = new DBCall(DBCall.QUERY,"SavingStatistics_Archive",params,true,true);
		
		PAWSServer.getDBLocalCalls().makeImmediateDBCall(dbCall);
	}
	
	/**
	 * Checks if an archive for this ou and date exists
	 * @param ou The ou in which to query
	 * @param date The date for the ou check
	 * @return Returns true if the archive has already been generated for this ou/date combination, false if doesn't exist
	 */
	public boolean validateArchive(String ou, Calendar date) {
		
		Vector<Object> params = new Vector<Object>();
		params.add(ou);
		params.add(new Timestamp(date.getTimeInMillis()));
		
		DBCall dbCall = new DBCall(DBCall.QUERY,"SavingStatistics_Validate",params,true,true);
		
		PAWSServer.getDBLocalCalls().makeImmediateDBCall(dbCall);
		
		try {
			
			Vector<Hashtable<String,Object>> results = (Vector<Hashtable<String,Object>>)dbCall.getResponse().get("QUERYRESULT");
			
			if (results.size() > 0) {
				
				if ((Integer)results.get(0).get("Result") == 1) {
					return true;
				} 
			}
		}
		catch (Exception ex) {
			ex.printStackTrace();
		}
		
		return false;
	}

	/**
	 * Returns the wattage consumption for a fqdn of a computer
	 * @param fqdn
	 * @param The datestamp in which to evaluate what the consumption was at this point in time
	 * @return The wattage is returned as default is the specific fqdn does not have a individual wattage coming the specified date.
	 */
	public int getComputerConsumption(String fqdn, Calendar refTime) {
		
		Vector<Object> params = new Vector<Object>();
		
		params.add(fqdn);
		params.add(new Timestamp(refTime.getTimeInMillis()));
		
		DBCall dbCall = new DBCall(DBCall.QUERY,SERVLETCALL_GET_COMPUTER_CONSUMPTION,params,true,true);
		
		PAWSServer.getDBLocalCalls().makeImmediateDBCall(dbCall);
		
		try {
			
			Vector<Hashtable<String,Object>> results = (Vector<Hashtable<String,Object>>)dbCall.getResponse().get("QUERYRESULT");
			
			if (results.size() > 0) {
				Hashtable<String,Object> node = results.get(0);
				
				return ((Integer)node.get("BaseConsumption")).intValue()+((Integer)node.get("MonitorConsumption")).intValue();
			}
		}
		catch (Exception ex) {
			ex.printStackTrace();
		}
		
		return 100;
	}
	
	/**
	 * Queries entries within the control value table e.g. paws sleeping active control value.
	 * @param key The value key
	 * @return The value in place within database
	 */
	public String getControlValue(String key) {
		
		String returnObj = null;
		
		Vector<Object> params = new Vector<Object>();
	
		params.add(key);
		DBCall dbCall = new DBCall(DBCall.QUERY,SERVLETCALL_GET_CENTRAL_CONTROL_ITEM,params,true,true);
			
		PAWSServer.getDBLocalCalls().makeImmediateDBCall(dbCall);
		
		try {
			
			Vector<Hashtable<String,Object>> results = (Vector<Hashtable<String,Object>>)dbCall.getResponse().get("QUERYRESULT");
			
			if (results.size() > 0) {
				
				Hashtable<String,Object> dataHash = results.get(0);
				returnObj = (String)dataHash.get("ControlValue");
			}			
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		
		return returnObj;	
	}

	/**
	 * Returns the last logout time on a particular computer
	 * @param fqdn The fqdn of the computer
	 * @return The last logout time, 0 is return is not logouts found
	 */
	public long getLastLogoutForComputer(String fqdn) {
		
		long returnObj = 0l;
		
		try {
			
			Vector<Object> params = new Vector<Object>();
			
			params.add(fqdn);
			DBCall dbCall = new DBCall(DBCall.QUERY,"Sessions_GetLastLoginForComputer",params,true,true);
			
			PAWSServer.getDBLocalCalls().makeImmediateDBCall(dbCall);
			
			Vector<Hashtable<String,Object>> results = (Vector<Hashtable<String,Object>>)dbCall.getResponse().get("QUERYRESULT");
			
			if (results.size() > 0) {
				
				Hashtable<String,Object> dataHash = results.get(0);
				
				if ((Timestamp)dataHash.get("LogoutTime") != null) {
					returnObj = ((Timestamp)dataHash.get("LogoutTime")).getTime();
				}
			}			
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		
		return returnObj;
	}
	
	/**
	 * Checks is a user is logged in currently to a computer
	 * @param computerName
	 * @return Returns true is computer logged into, false if not.
	 */
	public boolean isUserLoggedIn(String fqdn) {
		
		Vector<Object> params = new Vector<Object>();
		
		params.add(fqdn);
		DBCall dbCall = new DBCall(DBCall.QUERY,"Sessions_GetLastLoginForComputer",params,true,true);
		
		PAWSServer.getDBLocalCalls().makeImmediateDBCall(dbCall);
		
		try {
			
			Vector<Hashtable<String,Object>> results = (Vector<Hashtable<String,Object>>)dbCall.getResponse().get("QUERYRESULT");
			
			if (results.size() > 0) {
				
				if (results.get(0).get("LogoutTime") == null) {
					return true;
				}
			}			
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		
		return false;
	}

	/**
	 * Returns all the room groupings for the public workstation stats output. This specifies how rooms should be grouped in the stats output.
	 * @return A vector of hashtables containing this information.
	 */
	public Vector<Hashtable<String,Object>> getRoomGroupings() {
		
		Vector<Object> params = new Vector<Object>();
		
		DBCall dbCall = new DBCall(DBCall.QUERY,"LiveStatsBuildings_Get",params,true,true);
		PAWSServer.getDBLocalCalls().makeImmediateDBCall(dbCall);
		
		try {
			
			Vector<Hashtable<String,Object>> results = (Vector<Hashtable<String,Object>>)dbCall.getResponse().get("QUERYRESULT");
			
			return results;
			
		}
		catch (Exception e) {
			e.printStackTrace();
		}
	
		return null;
	}
	
	/**
	 * Returns a list of rooms for the required grouping
	 * @param roomGrouping The room grouping
	 * @param welshOption Welsh name option
	 * @return
	 */
	public Hashtable<String,String> getRoomStatsList(String roomGrouping, boolean welshOption) {
		
		Hashtable<String,String> returnObj = new Hashtable<String,String>();
		
		Vector<Object> params = new Vector<Object>();
		
		params.add(roomGrouping);
		DBCall dbCall = new DBCall(DBCall.QUERY,"LiveStatsRooms_GetByGroup",params,true,true);
		
		PAWSServer.getDBLocalCalls().makeImmediateDBCall(dbCall);
	
		try {
			
			Vector<Hashtable<String,Object>> results = (Vector<Hashtable<String,Object>>)dbCall.getResponse().get("QUERYRESULT");
			
			for (int i=0;i<results.size();i++) {				
				Hashtable<String,Object> dataHash = results.get(i);
				
				if (!welshOption) {
					returnObj.put((String)dataHash.get("OU"),(String)dataHash.get("Label1"));
				}
				else {
					returnObj.put((String)dataHash.get("OU"),(String)dataHash.get("Label2"));
				}
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
	
		return returnObj;
	}
	
	/**
	 * Refreshes the overall organisational savings statistics, sub divided by the ou
	 * @param ou The ou in which to refresh
	 * @param startDate The start date in which to refresh
	 * @param endDate The end month in which to refresh
	 */
	public void refreshOrgSavings(String ou, Calendar startDate, Calendar endDate) {
		
		Vector<Object> params = new Vector<Object>();
		
		params.add(ou);
		params.add(new Timestamp(startDate.getTimeInMillis()));
		params.add(new Timestamp(endDate.getTimeInMillis()));
		
		DBCall dbCall = new DBCall(DBCall.QUERY,"OrganisationSavingStatistics_Set",params,true,true);
		
		PAWSServer.getDBLocalCalls().makeImmediateDBCall(dbCall);
		
	}
	
	/**
	 * Returns the root ou (top of the tree) for all domains configured within the database
	 * @return Returns a vector of domain names mapped to the configured root ou within that domain
	 */
	public static Vector<Hashtable<String,Object>> getRootOUs() {
		
		Vector<Object> params = new Vector<Object>();
		
		// get list of root OUs...
		DBCall dbCall = new DBCall(DBCall.QUERY,"Domains_GetAll",params,true,true);
		
		PAWSServer.getDBLocalCalls().makeImmediateDBCall(dbCall);
		
		return (Vector<Hashtable<String,Object>>)dbCall.getResponse().get("QUERYRESULT");
		
	}

	/**
	 * A check to see if a computer is faulty - i.e. it hasn't been logged into within the specified time threshold
	 * @param fqdn The computer name
	 * @param lastLoginThreshold The threshold in which to check if a user has logged in during 
	 * @return Returns true if there is at least one login within threshold period, false if not
	 */
	public boolean isComputerFaulty(String fqdn, long lastLoginThreshold) {
		
        Vector<Object> params = new Vector<Object>();
		
		params.add(fqdn);
		
		DBCall dbCall = new DBCall(DBCall.QUERY,"Sessions_GetLastLoginForComputer",params,true,true);
		
		PAWSServer.getDBLocalCalls().makeImmediateDBCall(dbCall);
		
		try {
			
			Vector<Hashtable<String,Object>> results = (Vector<Hashtable<String,Object>>)dbCall.getResponse().get("QUERYRESULT");
			
			if (results.size() == 1) {
				long lastLoginTime = ((Long)results.get(0).get("LoginTime")).longValue();
				
				if (lastLoginTime < lastLoginThreshold) {
					return true;
				}
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		
		return false;
	}
	
}
